import os
from flask import Flask, request, jsonify, render_template, send_from_directory, redirect, url_for, session, flash
from flask_cors import CORS
from werkzeug.utils import secure_filename
from werkzeug.security import generate_password_hash, check_password_hash
import flask  # 添加这一行导入flask模块
import os
from flask import Flask, request, jsonify
from flask_cors import CORS
from werkzeug.utils import secure_filename
from flask_sqlalchemy import SQLAlchemy
import sqlalchemy  # 导入原始的sqlalchemy模块以获取版本号
import sys
import base64
import time
import datetime
from functools import wraps
import torch
import random

# 添加项目根目录到Python路径
root_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
if root_dir not in sys.path:
    sys.path.insert(0, root_dir)

from lmodels.predict import GarbagePredictor

app = Flask(__name__)
CORS(app)

# 配置文件上传
UPLOAD_FOLDER = 'uploads'
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg'}
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

# Flask配置
app.secret_key = 'garbage_classification_secret_key'  # 用于会话
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///garbage_classification.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 闪存消息配置
app.config['SESSION_TYPE'] = 'filesystem'

# 初始化数据库
db = SQLAlchemy(app)

# 用户模型
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    role = db.Column(db.String(20), default='user')  # 'user' 或 'admin'
    predictions = db.relationship('PredictionHistory', backref='user', lazy=True)
    
    def set_password(self, password):
        self.password_hash = generate_password_hash(password)
    
    def check_password(self, password):
        return check_password_hash(self.password_hash, password)
    
    def is_admin(self):
        return self.role == 'admin'
    
    def __repr__(self):
        return f'<User {self.username}>'

# 预测历史模型
class PredictionHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    image_path = db.Column(db.String(255), nullable=False)
    predicted_class = db.Column(db.String(50), nullable=False)
    confidence = db.Column(db.Float, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)

# 垃圾科普知识模型
class KnowledgeArticle(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    category = db.Column(db.String(50), nullable=False)  # 垃圾分类类别
    image_url = db.Column(db.String(255))  # 配图URL
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
    is_published = db.Column(db.Boolean, default=True)
    view_count = db.Column(db.Integer, default=0)
    
    author = db.relationship('User', backref='articles')
    
    def __repr__(self):
        return f'<KnowledgeArticle {self.title}>'

# 用户积分记录模型
class UserPoints(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    points = db.Column(db.Integer, default=0)  # 当前积分
    total_earned = db.Column(db.Integer, default=0)  # 累计获得积分
    level = db.Column(db.Integer, default=1)  # 用户等级
    level_name = db.Column(db.String(50), default='环保新手')  # 等级名称
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
    
    user = db.relationship('User', backref='points_record')

# 积分变动记录模型
class PointsHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    points_change = db.Column(db.Integer, nullable=False)  # 积分变动（正数为获得，负数为消费）
    reason = db.Column(db.String(100), nullable=False)  # 积分变动原因
    description = db.Column(db.String(255))  # 详细描述
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    
    user = db.relationship('User', backref='points_history')

# 成就系统模型
class Achievement(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)  # 成就名称
    description = db.Column(db.String(255), nullable=False)  # 成就描述
    icon_url = db.Column(db.String(255))  # 成就图标URL
    condition_type = db.Column(db.String(50), nullable=False)  # 条件类型：prediction_count, consecutive_days, accuracy_rate等
    condition_value = db.Column(db.Integer, nullable=False)  # 条件数值
    points_reward = db.Column(db.Integer, default=0)  # 完成成就获得的积分
    is_active = db.Column(db.Boolean, default=True)  # 是否启用
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)

# 用户成就记录模型
class UserAchievement(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    achievement_id = db.Column(db.Integer, db.ForeignKey('achievement.id'), nullable=False)
    achieved_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    
    user = db.relationship('User', backref='achievements')
    achievement = db.relationship('Achievement', backref='users_achieved')

# 用户行为统计模型
class UserStats(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    total_predictions = db.Column(db.Integer, default=0)  # 总识别次数
    correct_predictions = db.Column(db.Integer, default=0)  # 正确识别次数
    accuracy_rate = db.Column(db.Float, default=0.0)  # 准确率
    consecutive_days = db.Column(db.Integer, default=0)  # 连续使用天数
    last_active_date = db.Column(db.Date)  # 最后活跃日期
    favorite_category = db.Column(db.String(50))  # 最常识别的垃圾类型
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
    
    user = db.relationship('User', backref='stats')

# 系统统计模型（按日期统计）
class SystemStats(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.Date, nullable=False, unique=True)  # 统计日期
    active_users = db.Column(db.Integer, default=0)  # 活跃用户数
    total_predictions = db.Column(db.Integer, default=0)  # 总识别次数
    new_users = db.Column(db.Integer, default=0)  # 新注册用户数
    popular_category = db.Column(db.String(50))  # 热门垃圾类型
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)

# 论坛帖子模型
class ForumPost(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    category = db.Column(db.String(50), default='general')  # 帖子分类：general, tips, qa
    is_pinned = db.Column(db.Boolean, default=False)  # 是否置顶
    view_count = db.Column(db.Integer, default=0)  # 浏览次数
    like_count = db.Column(db.Integer, default=0)  # 点赞数
    reply_count = db.Column(db.Integer, default=0)  # 回复数
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
    
    author = db.relationship('User', backref='forum_posts')

# 论坛回复模型
class ForumReply(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    post_id = db.Column(db.Integer, db.ForeignKey('forum_post.id'), nullable=False)
    author_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    content = db.Column(db.Text, nullable=False)
    like_count = db.Column(db.Integer, default=0)  # 点赞数
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    
    post = db.relationship('ForumPost', backref='replies')
    author = db.relationship('User', backref='forum_replies')

# 用户点赞记录模型
class UserLike(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    target_type = db.Column(db.String(20), nullable=False)  # 'post' 或 'reply'
    target_id = db.Column(db.Integer, nullable=False)  # 帖子或回复的ID
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    
    user = db.relationship('User', backref='likes')

# 用户签到记录模型
class UserCheckin(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    checkin_date = db.Column(db.Date, nullable=False)
    points_earned = db.Column(db.Integer, default=5)  # 签到获得的积分
    consecutive_days = db.Column(db.Integer, default=1)  # 连续签到天数
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    
    user = db.relationship('User', backref='checkins')
    
    # 确保每个用户每天只能签到一次
    __table_args__ = (db.UniqueConstraint('user_id', 'checkin_date', name='unique_user_checkin'),)

# 积分商城商品模型
class PointsShopItem(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)  # 商品名称
    description = db.Column(db.String(255))  # 商品描述
    points_cost = db.Column(db.Integer, nullable=False)  # 所需积分
    item_type = db.Column(db.String(50), default='virtual')  # 商品类型：virtual, tip, badge
    content = db.Column(db.Text)  # 商品内容（如环保小贴士的内容）
    icon_url = db.Column(db.String(255))  # 商品图标
    is_available = db.Column(db.Boolean, default=True)  # 是否可兑换
    stock = db.Column(db.Integer, default=-1)  # 库存（-1表示无限）
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)

# 用户兑换记录模型
class UserPurchase(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    item_id = db.Column(db.Integer, db.ForeignKey('points_shop_item.id'), nullable=False)
    points_spent = db.Column(db.Integer, nullable=False)  # 花费的积分
    status = db.Column(db.String(20), default='completed')  # 兑换状态
    created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    
    user = db.relationship('User', backref='purchases')
    item = db.relationship('PointsShopItem', backref='purchases')

# 确保上传目录存在
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

# 初始化预测器
model_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models', 'best_model.pth')
if not os.path.exists(model_path):
    model_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '..', 'models', 'best_model.pth')
predictor = GarbagePredictor(model_path)

def allowed_file(filename):
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# 登录路由
@app.route('/login', methods=['GET', 'POST'])
def login():
    error_message = None
    
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        
        if not username or not password:
            error_message = '请输入用户名和密码'
            print(f"登录失败：用户名或密码为空，用户名: {username}")  # 调试信息
        else:
            print(f"尝试登录，用户名: {username}")  # 调试信息
            user = User.query.filter_by(username=username).first()
            print(f"数据库查找用户结果: {user}")  # 调试信息
            
            if user:
                password_check = user.check_password(password)
                print(f"密码验证结果: {password_check}")  # 调试信息
                if password_check:
                    session['user_id'] = user.id
                    session['username'] = user.username
                    print(f"登录成功，用户: {username}, 准备跳转到首页")  # 调试信息
                    return redirect(url_for('index'))
                else:
                    error_message = '密码错误'
                    print(f"登录失败：密码错误，用户: {username}")  # 调试信息
            else:
                error_message = '用户不存在'
                print(f"登录失败：用户不存在，用户名: {username}")  # 调试信息
    
    return render_template('login.html', error=error_message)

# 注册路由
@app.route('/register', methods=['GET', 'POST'])
def register():
    error_message = None
    
    if request.method == 'POST':
        username = request.form.get('username')
        email = request.form.get('email')
        password = request.form.get('password')
        confirm_password = request.form.get('confirm_password')
        
        if not username or not email or not password or not confirm_password:
            error_message = '请填写所有字段'
        elif password != confirm_password:
            error_message = '两次输入的密码不一致'
        else:
            existing_user = User.query.filter((User.username == username) | (User.email == email)).first()
            if existing_user:
                error_message = '用户名或邮箱已被注册'
            else:
                new_user = User(username=username, email=email)
                new_user.set_password(password)
                db.session.add(new_user)
                try:
                    db.session.commit()
                    return redirect(url_for('login'))
                except Exception as e:
                    db.session.rollback()
                    error_message = f'注册失败: {str(e)}'
    
    return render_template('register.html', error=error_message)

# 注销路由
@app.route('/logout')
def logout():
    session.pop('user_id', None)
    session.pop('username', None)
    return redirect(url_for('login'))

@app.route('/predict', methods=['POST'])
def predict():
    # 验证用户是否已登录
    if 'user_id' not in session:
        return jsonify({'error': '请先登录'}), 401

    if 'file' not in request.files:
        return jsonify({'error': '没有文件上传'}), 400
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': '没有选择文件'}), 400
    
    if file and allowed_file(file.filename):
        filename = secure_filename(file.filename)
        filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        file.save(filepath)
        
        try:
            # 进行预测
            result = predictor.predict(filepath)
            
            # 记录预测历史
            user = db.session.get(User, session['user_id'])
            predicted_class = result['predicted_class']
            confidence = result['probabilities'][predicted_class]
            
            prediction = PredictionHistory(
                user_id=user.id,
                image_path=filename,
                predicted_class=predicted_class,
                confidence=confidence
            )
            db.session.add(prediction)
            db.session.commit()
            
            # 更新用户统计数据（假设识别正确，实际应用中可以让用户确认）
            is_correct = confidence > 0.7  # 简单的正确性判断
            update_user_stats(user.id, predicted_class, is_correct)
            
            # 奖励积分
            points_earned = 10 if is_correct else 5
            add_points(user.id, points_earned, "垃圾识别", 
                      f"识别{predicted_class}，置信度{confidence:.2f}")
            
            # 清理上传的文件
            os.remove(filepath)
            
            # 添加积分信息到返回结果
            result['points_earned'] = points_earned
            result['is_correct'] = is_correct
            
            return jsonify(result)
        except Exception as e:
            # 确保清理上传的文件
            if os.path.exists(filepath):
                os.remove(filepath)
            return jsonify({'error': str(e)}), 500
    
    return jsonify({'error': '不支持的文件类型'}), 400

@app.route('/health', methods=['GET'])
def health_check():
    return jsonify({'status': 'healthy'})

@app.route('/', methods=['GET', 'POST'])
def index():
    print(f"访问首页，session内容: {dict(session)}")  # 调试信息
    # 验证用户是否已登录
    if 'user_id' not in session:
        print("用户未登录，跳转到登录页面")  # 调试信息
        return redirect(url_for('login'))

    # 获取当前用户
    current_user = db.session.get(User, session['user_id'])
    is_admin = current_user.is_admin() if current_user else False

    prediction_result = None
    image_data = None
    error_message = None
    probabilities = None
    
    if request.method == 'POST' and 'file' in request.files:
        file = request.files['file']
        if file.filename != '' and allowed_file(file.filename):
            filename = secure_filename(file.filename)
            filepath = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            file.save(filepath)
            
            try:
                # 进行预测
                prediction_result = predictor.predict(filepath)
                print("预测结果:", prediction_result)  # 输出调试信息
                
                # 获取概率信息
                if 'probabilities' in prediction_result:
                    probabilities = prediction_result['probabilities']
                
                # 记录预测历史
                prediction = PredictionHistory(
                    user_id=current_user.id,
                    image_path=filename,
                    predicted_class=prediction_result['predicted_class'],
                    confidence=prediction_result['probabilities'][prediction_result['predicted_class']]
                )
                db.session.add(prediction)
                db.session.commit()
                
                # 读取图像并转换为base64以在网页中显示
                with open(filepath, 'rb') as img_file:
                    image_data = base64.b64encode(img_file.read()).decode('utf-8')
                
                # 清理上传的文件
                os.remove(filepath)
            except Exception as e:
                # 确保清理上传的文件
                if os.path.exists(filepath):
                    os.remove(filepath)
                error_message = str(e)
                print("预测错误:", error_message)  # 输出调试信息
    
    # 构建预测结果和概率显示内容
    category_display = ""
    probabilities_display = ""
    
    if prediction_result:
        # 预测类别显示
        if isinstance(prediction_result, dict):
            if "predicted_class" in prediction_result:
                category_display = f'<div class="prediction-result">预测类别: {prediction_result["predicted_class"]}</div>'
            elif "category" in prediction_result:
                category_display = f'<div class="prediction-result">预测类别: {prediction_result["category"]}</div>'
        
        # 概率显示
        if probabilities and isinstance(probabilities, dict):
            probabilities_display = '<div class="probabilities">'
            probabilities_display += '<h3>各类别概率:</h3>'
            probabilities_display += '<ul class="prob-list">'
            for cls, prob in probabilities.items():
                probabilities_display += f'<li>{cls}: {prob:.4f}</li>'
            probabilities_display += '</ul></div>'
    
    return render_template('index.html', 
                         username=session['username'],
                         is_admin=is_admin,
                         prediction_result=prediction_result,
                         image_data=image_data,
                         error_message=error_message,
                         probabilities=probabilities,
                         category_display=category_display,
                         probabilities_display=probabilities_display)

@app.route('/static/<path:path>')
def serve_static(path):
    return send_from_directory('static', path)

# 管理员访问装饰器
def admin_required(func):
    @wraps(func)
    def decorated_function(*args, **kwargs):
        if 'user_id' not in session:
            return redirect(url_for('login'))
            
        user = db.session.get(User, session['user_id'])
        if not user or not user.is_admin():
            flash('您没有权限访问该页面')
            return redirect(url_for('index'))
            
        return func(*args, **kwargs)
    return decorated_function

# 后台首页
@app.route('/admin')
@admin_required
def admin_dashboard():
    # 获取所有用户数据
    users = User.query.all()
    user_count = len(users)
    
    # 获取总预测次数
    total_predictions = PredictionHistory.query.count()
    
    # 构建最近用户表格
    recent_users_rows = ""
    for user in sorted(users, key=lambda u: u.created_at, reverse=True)[:5]:
        role_badge = '<span class="admin-badge">管理员</span>' if user.role == 'admin' else '<span class="user-badge">普通用户</span>'
        user_row = f"""
        <tr>
            <td>{user.id}</td>
            <td>{user.username}</td>
            <td>{user.email}</td>
            <td>{role_badge}</td>
            <td>{user.created_at.strftime('%Y-%m-%d %H:%M')}</td>
        </tr>
        """
        recent_users_rows += user_row
    
    return render_template('admin_dashboard.html',
                         user_count=user_count,
                         total_predictions=total_predictions,
                         recent_users=sorted(users, key=lambda u: u.created_at, reverse=True)[:5])

# 用户管理页面
@app.route('/admin/users')
@admin_required
def admin_users():
    users = User.query.all()
    
    # 构建用户表格行
    user_rows = ""
    for user in users:
        admin_action = ""
        if user.role == 'admin':
            admin_action = f'<a href="/admin/toggle_admin/{user.id}?action=remove" class="action-button admin-button" onclick="return confirm(\'确定要移除管理员权限吗？\')">移除管理员</a>'
        else:
            admin_action = f'<a href="/admin/toggle_admin/{user.id}?action=add" class="action-button admin-button" onclick="return confirm(\'确定要设为管理员吗？\')">设为管理员</a>'
            
        role_badge = ""
        if user.role == 'admin':
            role_badge = '<span class="admin-badge">管理员</span>'
        else:
            role_badge = '<span class="user-badge">普通用户</span>'
            
        user_row = f"""
        <tr>
            <td>{user.id}</td>
            <td>{user.username}</td>
            <td>{user.email}</td>
            <td>{role_badge}</td>
            <td>{user.created_at.strftime('%Y-%m-%d %H:%M')}</td>
            <td>
                <a href="/admin/edit_user/{user.id}" class="action-button edit-button">编辑</a>
                <a href="/admin/delete_user/{user.id}" class="action-button delete-button" onclick="return confirm('确定要删除此用户吗？')">删除</a>
                {admin_action}
            </td>
        </tr>
        """
        user_rows += user_row
    
    return render_template('admin_users.html', users=users)

# 系统设置页面
@app.route('/admin/settings')
@admin_required
def admin_settings():
    # 获取总预测次数
    total_predictions = PredictionHistory.query.count()
    
    # 获取可用模型列表
    model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models')
    if not os.path.exists(model_dir):
        model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '..', 'models')
    
    available_models = []
    if os.path.exists(model_dir):
        available_models = [f for f in os.listdir(model_dir) if f.endswith('.pth')]
    
    # 检查当前是否使用优化模型
    use_enhanced_model = os.environ.get('USE_ENHANCED_MODEL', 'false').lower() == 'true'
    
    # 构建模型卡片HTML
    model_cards_html = ""
    if available_models:
        for model_file in available_models:
            model_path_full = os.path.join(model_dir, model_file)
            is_active = os.path.basename(model_path) == model_file
            file_size = os.path.getsize(model_path_full) / (1024*1024)
            
            model_cards_html += f"""
            <div class="model-card {'active-model' if is_active else ''}">
                <div class="model-info">
                    <h4>{model_file}</h4>
                    <p>路径: {model_path_full}</p>
                    <p>大小: {file_size:.2f} MB</p>
                </div>
                <div class="model-actions">
                    <a href="/admin/load_model?model={model_file}" class="model-button load-button">加载此模型</a>
                    <a href="/admin/delete_model?model={model_file}" class="model-button delete-button" onclick="return confirm('确定要删除此模型吗？')">删除</a>
                </div>
            </div>
            """
    else:
        model_cards_html = '<p>没有可用的模型文件</p>'
    
    # JavaScript代码部分 - 分离出来避免在f-string中使用JS注释
    js_code = """
    function openTab(evt, tabName) {
        // 隐藏所有标签内容
        var tabcontent = document.getElementsByClassName("tab-content");
        for (var i = 0; i < tabcontent.length; i++) {
            tabcontent[i].className = tabcontent[i].className.replace(" active", "");
        }
        
        // 移除所有标签的active类
        var tabs = document.getElementsByClassName("tab");
        for (var i = 0; i < tabs.length; i++) {
            tabs[i].className = tabs[i].className.replace(" active", "");
        }
        
        // 显示当前标签内容并添加active类到按钮
        document.getElementById(tabName).className += " active";
        evt.currentTarget.className += " active";
    }
    """
    
    # 准备模型数据
    models_data = []
    if available_models:
        for model_file in available_models:
            model_path_full = os.path.join(model_dir, model_file)
            is_active = os.path.basename(model_path) == model_file
            file_size = os.path.getsize(model_path_full) / (1024*1024)
            models_data.append({
                'filename': model_file,
                'path': model_path_full,
                'size': file_size,
                'is_active': is_active
            })
    
    return render_template('admin_settings.html',
                         total_predictions=total_predictions,
                         available_models=models_data,
                         use_enhanced_model=use_enhanced_model,
                         model_path=model_path)

# 科普知识管理路由
@app.route('/admin/knowledge')
@admin_required
def admin_knowledge():
    articles = KnowledgeArticle.query.order_by(KnowledgeArticle.created_at.desc()).all()
    return render_template('admin_knowledge.html', articles=articles)


# 管理员社区管理
@app.route('/admin/forum')
@admin_required
def admin_forum():
    posts = ForumPost.query.order_by(ForumPost.created_at.desc()).all()
    return render_template('admin_forum.html', posts=posts)


# 管理员积分商城管理
@app.route('/admin/points_shop')
@admin_required
def admin_points_shop():
    items = PointsShopItem.query.all()
    return render_template('admin_points_shop.html', items=items)


# 添加积分商城商品
@app.route('/admin/points_shop/add', methods=['GET', 'POST'])
@admin_required
def admin_add_shop_item():
    if request.method == 'POST':
        name = request.form.get('name')
        description = request.form.get('description')
        points_cost = int(request.form.get('points_cost'))
        stock = int(request.form.get('stock', -1))
        is_available = 'is_available' in request.form
        
        item = PointsShopItem(
            name=name,
            description=description,
            points_cost=points_cost,
            stock=stock,
            is_available=is_available
        )
        
        db.session.add(item)
        db.session.commit()
        flash('商品添加成功！', 'success')
        return redirect(url_for('admin_points_shop'))
    
    return render_template('admin_add_shop_item.html')


# 编辑积分商城商品
@app.route('/admin/points_shop/edit/<int:item_id>', methods=['GET', 'POST'])
@admin_required
def admin_edit_shop_item(item_id):
    item = PointsShopItem.query.get_or_404(item_id)
    
    if request.method == 'POST':
        item.name = request.form.get('name')
        item.description = request.form.get('description')
        item.points_cost = int(request.form.get('points_cost'))
        item.stock = int(request.form.get('stock', -1))
        item.is_available = 'is_available' in request.form
        
        db.session.commit()
        flash('商品更新成功！', 'success')
        return redirect(url_for('admin_points_shop'))
    
    return render_template('admin_edit_shop_item.html', item=item)


# 删除积分商城商品
@app.route('/admin/points_shop/delete/<int:item_id>')
@admin_required
def admin_delete_shop_item(item_id):
    item = PointsShopItem.query.get_or_404(item_id)
    db.session.delete(item)
    db.session.commit()
    flash('商品删除成功！', 'success')
    return redirect(url_for('admin_points_shop'))


# 删除论坛帖子
@app.route('/admin/forum/delete_post/<int:post_id>')
@admin_required
def admin_delete_post(post_id):
    post = ForumPost.query.get_or_404(post_id)
    # 删除相关的回复
    ForumReply.query.filter_by(post_id=post_id).delete()
    # 删除相关的点赞记录
    UserLike.query.filter_by(target_type='post', target_id=post_id).delete()
    # 删除帖子
    db.session.delete(post)
    db.session.commit()
    flash('帖子删除成功！', 'success')
    return redirect(url_for('admin_forum'))


# 置顶/取消置顶帖子
@app.route('/admin/forum/toggle_pin/<int:post_id>')
@admin_required
def admin_toggle_pin(post_id):
    post = ForumPost.query.get_or_404(post_id)
    post.is_pinned = not post.is_pinned
    db.session.commit()
    action = '置顶' if post.is_pinned else '取消置顶'
    flash(f'帖子{action}成功！', 'success')
    return redirect(url_for('admin_forum'))

# 添加科普文章
@app.route('/admin/knowledge/add', methods=['GET', 'POST'])
@admin_required
def add_knowledge():
    if request.method == 'POST':
        title = request.form.get('title')
        content = request.form.get('content')
        category = request.form.get('category')
        image_url = request.form.get('image_url')
        
        if not title or not content or not category:
            flash('请填写所有必填字段', 'error')
            return render_template('add_knowledge.html')
        
        article = KnowledgeArticle(
            title=title,
            content=content,
            category=category,
            image_url=image_url,
            author_id=session['user_id']
        )
        
        try:
            db.session.add(article)
            db.session.commit()
            flash('科普文章添加成功！', 'success')
            return redirect(url_for('admin_knowledge'))
        except Exception as e:
            db.session.rollback()
            flash(f'添加失败: {str(e)}', 'error')
    
    return render_template('add_knowledge.html')

# 编辑科普文章
@app.route('/admin/knowledge/edit/<int:article_id>', methods=['GET', 'POST'])
@admin_required
def edit_knowledge(article_id):
    article = KnowledgeArticle.query.get_or_404(article_id)
    
    if request.method == 'POST':
        article.title = request.form.get('title')
        article.content = request.form.get('content')
        article.category = request.form.get('category')
        article.image_url = request.form.get('image_url')
        article.updated_at = datetime.datetime.utcnow()
        
        try:
            db.session.commit()
            flash('科普文章更新成功！', 'success')
            return redirect(url_for('admin_knowledge'))
        except Exception as e:
            db.session.rollback()
            flash(f'更新失败: {str(e)}', 'error')
    
    return render_template('edit_knowledge.html', article=article)

# 删除科普文章
@app.route('/admin/knowledge/delete/<int:article_id>')
@admin_required
def delete_knowledge(article_id):
    article = KnowledgeArticle.query.get_or_404(article_id)
    
    try:
        db.session.delete(article)
        db.session.commit()
        flash('科普文章删除成功！', 'success')
    except Exception as e:
        db.session.rollback()
        flash(f'删除失败: {str(e)}', 'error')
    
    return redirect(url_for('admin_knowledge'))

# 前台科普知识展示
@app.route('/knowledge')
def knowledge():
    category = request.args.get('category', '')
    if category:
        articles = KnowledgeArticle.query.filter_by(category=category, is_published=True).order_by(KnowledgeArticle.created_at.desc()).all()
    else:
        articles = KnowledgeArticle.query.filter_by(is_published=True).order_by(KnowledgeArticle.created_at.desc()).all()
    
    # 获取所有分类
    categories = db.session.query(KnowledgeArticle.category).distinct().all()
    categories = [cat[0] for cat in categories]
    
    return render_template('knowledge_list.html', articles=articles, selected_category=category)

# 科普文章详情
@app.route('/knowledge/<int:article_id>')
def knowledge_detail(article_id):
    article = KnowledgeArticle.query.get_or_404(article_id)
    
    # 增加浏览次数
    article.view_count += 1
    db.session.commit()
    
    return render_template('knowledge_detail.html', article=article)

# 删除所有预测历史
@app.route('/admin/delete_predictions', methods=['POST'])
@admin_required
def delete_predictions():
    try:
        # 删除所有预测历史记录
        PredictionHistory.query.delete()
        db.session.commit()
        flash('所有预测历史数据已成功删除')
    except Exception as e:
        db.session.rollback()
        flash(f'删除预测历史数据失败: {str(e)}')
    
    return redirect(url_for('admin_settings'))

# 切换用户管理员状态
@app.route('/admin/toggle_admin/<int:user_id>')
@admin_required
def toggle_admin(user_id):
    action = request.args.get('action')
    if action not in ['add', 'remove']:
        flash('无效的操作')
        return redirect(url_for('admin_users'))
    
    user = User.query.get_or_404(user_id)
    
    # 防止移除自己的管理员权限
    if user.id == session['user_id'] and action == 'remove':
        flash('不能移除自己的管理员权限')
        return redirect(url_for('admin_users'))
    
    if action == 'add':
        user.role = 'admin'
        db.session.commit()
        flash(f'已将 {user.username} 设为管理员')
    else:
        user.role = 'user'
        db.session.commit()
        flash(f'已移除 {user.username} 的管理员权限')
    
    return redirect(url_for('admin_users'))

# 编辑用户
@app.route('/admin/edit_user/<int:user_id>', methods=['GET', 'POST'])
@admin_required
def edit_user(user_id):
    user = User.query.get_or_404(user_id)
    error_message = None
    
    if request.method == 'POST':
        username = request.form.get('username')
        email = request.form.get('email')
        password = request.form.get('password')
        
        if not username or not email:
            error_message = '用户名和邮箱不能为空'
        else:
            # 检查用户名是否已存在(排除当前用户)
            existing_user = User.query.filter(User.username == username, User.id != user_id).first()
            if existing_user:
                error_message = '用户名已被使用'
            else:
                # 检查邮箱是否已存在(排除当前用户)
                existing_email = User.query.filter(User.email == email, User.id != user_id).first()
                if existing_email:
                    error_message = '邮箱已被注册'
                else:
                    # 更新用户信息
                    user.username = username
                    user.email = email
                    if password:
                        user.set_password(password)
                    
                    try:
                        db.session.commit()
                        flash('用户信息已成功更新')
                        return redirect(url_for('admin_users'))
                    except Exception as e:
                        db.session.rollback()
                        error_message = f'更新失败: {str(e)}'
    
    return render_template('edit_user.html', user=user, error=error_message)

# 删除用户
@app.route('/admin/delete_user/<int:user_id>')
@admin_required
def delete_user(user_id):
    user = User.query.get_or_404(user_id)
    
    # 防止删除自己
    if user.id == session['user_id']:
        flash('不能删除自己的账户')
        return redirect(url_for('admin_users'))
    
    db.session.delete(user)
    db.session.commit()
    flash(f'用户 {user.username} 已被删除')
    
    return redirect(url_for('admin_users'))

# 更新性能设置
@app.route('/admin/update_performance', methods=['POST'])
@admin_required
def update_performance():
    try:
        # 获取表单数据
        use_enhanced_model = 'use_enhanced_model' in request.form
        batch_size = request.form.get('batch_size', '32')
        num_workers = request.form.get('num_workers', '4')
        
        # 更新环境变量
        os.environ['USE_ENHANCED_MODEL'] = 'true' if use_enhanced_model else 'false'
        os.environ['BATCH_SIZE'] = batch_size
        os.environ['NUM_WORKERS'] = num_workers
        
        # 如果使用增强模型，需要更新预测器
        global predictor
        if use_enhanced_model:
            # 导入增强模型
            from lmodels.train import EnhancedGarbageClassifier
            model_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models', 'enhanced_model.pth')
            if os.path.exists(model_path):
                predictor.model = EnhancedGarbageClassifier(num_classes=6)
                predictor.model.load_state_dict(torch.load(model_path, map_location=predictor.device))
                predictor.model.to(predictor.device)
                predictor.model.eval()
                flash('已成功切换到优化后的模型')
            else:
                flash('找不到优化后的模型文件，请先训练或上传')
                os.environ['USE_ENHANCED_MODEL'] = 'false'
        else:
            # 恢复使用原始模型
            from lmodels.train import GarbageClassifier
            model_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models', 'best_model.pth')
            if os.path.exists(model_path):
                predictor.model = GarbageClassifier(num_classes=6)
                predictor.model.load_state_dict(torch.load(model_path, map_location=predictor.device))
                predictor.model.to(predictor.device)
                predictor.model.eval()
                flash('已成功切换到标准模型')
            else:
                flash('找不到标准模型文件')
        
        flash('性能设置已更新')
    except Exception as e:
        flash(f'更新性能设置时出错: {str(e)}')
    
    return redirect(url_for('admin_settings'))

# 上传模型
@app.route('/admin/upload_model', methods=['POST'])
@admin_required
def upload_model():
    if 'model_file' not in request.files:
        flash('没有选择文件')
        return redirect(url_for('admin_settings'))
    
    file = request.files['model_file']
    if file.filename == '':
        flash('没有选择文件')
        return redirect(url_for('admin_settings'))
    
    if file and file.filename.endswith('.pth'):
        try:
            filename = secure_filename(file.filename)
            model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models')
            
            # 确保模型目录存在
            if not os.path.exists(model_dir):
                model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '..', 'models')
                if not os.path.exists(model_dir):
                    os.makedirs(model_dir)
            
            filepath = os.path.join(model_dir, filename)
            file.save(filepath)
            flash(f'模型 {filename} 已成功上传')
        except Exception as e:
            flash(f'上传模型时出错: {str(e)}')
    else:
        flash('不支持的文件类型，只接受 .pth 文件')
    
    return redirect(url_for('admin_settings'))

# 加载模型
@app.route('/admin/load_model')
@admin_required
def load_model():
    model_name = request.args.get('model')
    if not model_name:
        flash('未指定模型文件')
        return redirect(url_for('admin_settings'))
    
    try:
        model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models')
        if not os.path.exists(model_dir):
            model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '..', 'models')
        
        model_path = os.path.join(model_dir, model_name)
        if not os.path.exists(model_path):
            flash(f'找不到模型文件: {model_name}')
            return redirect(url_for('admin_settings'))
        
        # 更新全局预测器
        global predictor
        use_enhanced_model = os.environ.get('USE_ENHANCED_MODEL', 'false').lower() == 'true'
        
        if use_enhanced_model:
            from lmodels.train import EnhancedGarbageClassifier
            predictor.model = EnhancedGarbageClassifier(num_classes=6)
        else:
            from lmodels.train import GarbageClassifier
            predictor.model = GarbageClassifier(num_classes=6)
        
        predictor.model.load_state_dict(torch.load(model_path, map_location=predictor.device))
        predictor.model.to(predictor.device)
        predictor.model.eval()
        
        flash(f'模型 {model_name} 已成功加载')
    except Exception as e:
        flash(f'加载模型时出错: {str(e)}')
    
    return redirect(url_for('admin_settings'))

# 删除模型
@app.route('/admin/delete_model')
@admin_required
def delete_model():
    model_name = request.args.get('model')
    if not model_name:
        flash('未指定模型文件')
        return redirect(url_for('admin_settings'))
    
    try:
        model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models')
        if not os.path.exists(model_dir):
            model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '..', 'models')
        
        model_path = os.path.join(model_dir, model_name)
        if not os.path.exists(model_path):
            flash(f'找不到模型文件: {model_name}')
            return redirect(url_for('admin_settings'))
        
        # 检查当前加载的模型是否为要删除的模型
        current_model_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models', 'best_model.pth')
        if not os.path.exists(current_model_path):
            current_model_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '..', 'models', 'best_model.pth')
        
        if os.path.samefile(model_path, current_model_path):
            flash('无法删除当前正在使用的模型')
            return redirect(url_for('admin_settings'))
        
        # 删除模型文件
        os.remove(model_path)
        flash(f'模型 {model_name} 已成功删除')
    except Exception as e:
        flash(f'删除模型时出错: {str(e)}')
    
    return redirect(url_for('admin_settings'))

# 更新应用设置
@app.route('/admin/update_settings', methods=['POST'])
@admin_required
def update_settings():
    try:
        app_name = request.form.get('app_name', '垃圾分类系统')
        model_path = request.form.get('model_path', '')
        allowed_extensions = request.form.get('allowed_extensions', 'png,jpg,jpeg')
        
        # 更新环境变量或配置文件
        app.config['APP_NAME'] = app_name
        if model_path and os.path.exists(model_path):
            global predictor
            try:
                # 尝试加载新模型
                use_enhanced_model = os.environ.get('USE_ENHANCED_MODEL', 'false').lower() == 'true'
                
                if use_enhanced_model:
                    from lmodels.train import EnhancedGarbageClassifier
                    predictor.model = EnhancedGarbageClassifier(num_classes=6)
                else:
                    from lmodels.train import GarbageClassifier
                    predictor.model = GarbageClassifier(num_classes=6)
                
                predictor.model.load_state_dict(torch.load(model_path, map_location=predictor.device))
                predictor.model.to(predictor.device)
                predictor.model.eval()
                flash('已成功加载指定的模型')
            except Exception as e:
                flash(f'加载模型时出错: {str(e)}')
        
        # 更新允许的文件扩展名
        global ALLOWED_EXTENSIONS
        ALLOWED_EXTENSIONS = set(ext.strip() for ext in allowed_extensions.split(','))
        
        flash('应用设置已更新')
    except Exception as e:
        flash(f'更新设置时出错: {str(e)}')
    
    return redirect(url_for('admin_settings'))

# 添加模型训练功能
@app.route('/admin/train_model', methods=['GET', 'POST'])
@admin_required
def train_model():
    if request.method == 'POST':
        try:
            # 获取训练参数
            num_epochs = int(request.form.get('num_epochs', '5'))
            batch_size = int(request.form.get('batch_size', '32'))
            learning_rate = float(request.form.get('learning_rate', '0.001'))
            use_enhanced = 'use_enhanced' in request.form
            
            # 创建模型保存目录
            model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'models')
            if not os.path.exists(model_dir):
                model_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), '..', 'models')
                if not os.path.exists(model_dir):
                    os.makedirs(model_dir)
            
            # 导入训练函数和数据集
            from lmodels.train import train_model as train_func
            from lmodels.train import GarbageDataset, EnhancedGarbageClassifier, GarbageClassifier
            from torch.utils.data import DataLoader
            import torch.optim as optim
            import torch.nn as nn
            from torchvision import transforms
            
            # 设置数据预处理
            data_transforms = {
                'train': transforms.Compose([
                    transforms.RandomResizedCrop(224),
                    transforms.RandomHorizontalFlip(),
                    transforms.RandomRotation(15),
                    transforms.ColorJitter(brightness=0.2, contrast=0.2, saturation=0.2),
                    transforms.ToTensor(),
                    transforms.Normalize([0.485, 0.456, 0.406], [0.229, 0.224, 0.225])
                ]),
                'val': transforms.Compose([
                    transforms.Resize(256),
                    transforms.CenterCrop(224),
                    transforms.ToTensor(),
                    transforms.Normalize([0.485, 0.456, 0.406], [0.229, 0.224, 0.225])
                ])
            }
            
            # 准备数据集和加载器
            data_dir = os.path.join('data', 'raw', 'trashnet-master', 'data', 'dataset-resized')
            train_dataset = GarbageDataset(data_dir, transform=data_transforms['train'])
            val_dataset = GarbageDataset(data_dir, transform=data_transforms['val'])
            
            train_loader = DataLoader(train_dataset, batch_size=batch_size, shuffle=True, num_workers=4)
            val_loader = DataLoader(val_dataset, batch_size=batch_size, shuffle=False, num_workers=4)
            
            # 定义模型保存路径
            model_save_path = os.path.join(model_dir, 'enhanced_model.pth' if use_enhanced else 'new_model.pth')
            
            # 创建模型
            if use_enhanced:
                model = EnhancedGarbageClassifier(num_classes=6)
            else:
                model = GarbageClassifier(num_classes=6)
            
            # 设置损失函数和优化器
            criterion = nn.CrossEntropyLoss()
            optimizer = optim.Adam(model.parameters(), lr=learning_rate)
            
            # 训练模型
            device = torch.device('cuda' if torch.cuda.is_available() else 'cpu')
            best_acc = train_func(model, train_loader, val_loader, criterion, optimizer, num_epochs=num_epochs, device=device)
            
            flash(f'模型训练完成! 最佳准确率: {best_acc:.4f}')
            
            # 更新当前模型
            global predictor
            if use_enhanced:
                os.environ['USE_ENHANCED_MODEL'] = 'true'
                predictor.model = EnhancedGarbageClassifier(num_classes=6)
            else:
                predictor.model = GarbageClassifier(num_classes=6)
            
            predictor.model.load_state_dict(torch.load(model_save_path, map_location=predictor.device))
            predictor.model.to(predictor.device)
            predictor.model.eval()
            
            return redirect(url_for('admin_settings'))
        except Exception as e:
            flash(f'训练模型时出错: {str(e)}')
            return redirect(url_for('train_model'))
    
    # 创建HTML模板字符串
    html_template = """
    <html>
        <head>
            <title>训练模型 - 垃圾分类系统</title>
            <style>
                body {
                    font-family: Arial, sans-serif;
                    max-width: 800px;
                    margin: 0 auto;
                    padding: 20px;
                }
                h1, h2 {
                    color: #2c3e50;
                }
                .header {
                    display: flex;
                    justify-content: space-between;
                    align-items: center;
                    margin-bottom: 20px;
                    padding-bottom: 10px;
                    border-bottom: 1px solid #eee;
                }
                .back-button {
                    padding: 5px 10px;
                    background-color: #3498db;
                    color: white;
                    border: none;
                    border-radius: 4px;
                    cursor: pointer;
                    text-decoration: none;
                }
                .form-container {
                    background-color: #f9f9f9;
                    padding: 20px;
                    border-radius: 8px;
                    margin-top: 20px;
                }
                .form-group {
                    margin-bottom: 15px;
                }
                label {
                    display: block;
                    margin-bottom: 5px;
                    font-weight: bold;
                }
                input[type="number"],
                input[type="text"] {
                    width: 100%;
                    padding: 8px;
                    border: 1px solid #ddd;
                    border-radius: 4px;
                    box-sizing: border-box;
                }
                .checkbox-group {
                    margin-top: 8px;
                }
                .submit-button {
                    padding: 10px 20px;
                    background-color: #2ecc71;
                    color: white;
                    border: none;
                    border-radius: 5px;
                    cursor: pointer;
                    margin-top: 10px;
                }
                .note {
                    color: #7f8c8d;
                    font-size: 0.9em;
                    margin-top: 5px;
                }
                .warning {
                    color: #e74c3c;
                    font-weight: bold;
                    margin: 20px 0;
                    padding: 10px;
                    background-color: #fadbd8;
                    border-radius: 5px;
                }
            </style>
        </head>
        <body>
            <div class="header">
                <h1>训练新模型</h1>
                <a href="/admin/settings" class="back-button">返回设置</a>
            </div>
            
            <div class="warning">
                注意: 训练过程可能需要较长时间，取决于服务器性能和数据集大小。训练期间网页将不会响应。
            </div>
            
            <div class="form-container">
                <form action="/admin/train_model" method="post">
                    <div class="form-group">
                        <label for="num_epochs">训练轮数 (Epochs)</label>
                        <input type="number" id="num_epochs" name="num_epochs" value="5" min="1" max="50">
                        <div class="note">推荐值: 5-25。轮数越多，训练越充分，但时间也越长。</div>
                    </div>
                    
                    <div class="form-group">
                        <label for="batch_size">批处理大小 (Batch Size)</label>
                        <input type="number" id="batch_size" name="batch_size" value="32" min="1" max="128">
                        <div class="note">推荐值: 16-64。较大的批处理大小可以加快训练，但需要更多内存。</div>
                    </div>
                    
                    <div class="form-group">
                        <label for="learning_rate">学习率 (Learning Rate)</label>
                        <input type="text" id="learning_rate" name="learning_rate" value="0.001">
                        <div class="note">推荐值: 0.0001-0.01。较小的学习率训练更稳定但较慢。</div>
                    </div>
                    
                    <div class="form-group">
                        <label>模型类型</label>
                        <div class="checkbox-group">
                            <input type="checkbox" id="use_enhanced" name="use_enhanced">
                            <label for="use_enhanced" style="display: inline; font-weight: normal;">使用优化后的增强模型 (带注意力机制)</label>
                        </div>
                        <div class="note">增强模型具有更复杂的结构和注意力机制，可能有更好的性能，但训练时间更长。</div>
                    </div>
                    
                    <button type="submit" class="submit-button">开始训练</button>
                </form>
            </div>
        </body>
    </html>
    """
    
    return html_template

# 创建数据库和表
with app.app_context():
    import os
    
    # 不再删除数据库文件，改为检查数据库是否存在，不存在才创建
    db_file_exists = False
    
    db_paths = [
        'garbage_classification.db',  # 当前目录
        os.path.join('instance', 'garbage_classification.db')  # Flask默认实例文件夹
    ]
    
    for db_path in db_paths:
        if os.path.exists(db_path):
            db_file_exists = True
            print(f"使用现有数据库文件: {db_path}")
    
    # 创建数据库表（如果不存在）
    db.create_all()
    
    # 仅在数据库为空时创建默认用户
    if User.query.count() == 0:
        default_user = User(username='admin', email='admin@example.com', role='admin')
        default_user.set_password('admin')
        db.session.add(default_user)
        db.session.commit()
        print("已创建默认用户: admin/admin (管理员)")
        
        # 添加默认科普知识
        default_articles = [
            {
                'title': '可回收垃圾分类指南',
                'content': '''可回收垃圾是指适宜回收和资源利用的垃圾，主要包括：

**纸类：**
- 报纸、杂志、图书、各种包装纸
- 办公用纸、广告纸片
- 纸盒、纸箱等

**塑料：**
- 各种塑料袋、塑料包装物
- 塑料容器、塑料玩具
- 泡沫塑料、塑料餐具等

**金属：**
- 易拉罐、铁皮罐头盒
- 废铁、废铜、废铝等

**玻璃：**
- 玻璃瓶、玻璃杯
- 窗玻璃、镜子等

**织物：**
- 旧纺织衣物、纺织制品
- 废弃衣服、包、鞋等

**注意事项：**
投放前请清洁干净，去除食物残渣，保持干燥。''',
                'category': '可回收垃圾',
                'image_url': '',
                'author_id': 1
            },
            {
                'title': '有害垃圾处理须知',
                'content': '''有害垃圾是指对人体健康或自然环境造成直接或潜在危害的垃圾，主要包括：

**电池类：**
- 充电电池、纽扣电池
- 蓄电池等各类电池

**灯管类：**
- 荧光灯管、节能灯
- LED灯等含汞灯具

**药品类：**
- 过期药品、药品包装
- 医疗器械等

**化学品：**
- 油漆、涂料及其包装物
- 杀虫剂、消毒剂
- 胶水、指甲油等

**电子产品：**
- 废旧手机、电脑
- 打印机墨盒、硒鼓等

**处理要求：**
有害垃圾应单独收集，不可混入其他垃圾中，投放到专门的有害垃圾收集点。''',
                'category': '有害垃圾',
                'image_url': '',
                'author_id': 1
            },
            {
                'title': '厨余垃圾分类要点',
                'content': '''厨余垃圾是指家庭、餐厅、食堂、市场等产生的食物残余和食品加工废料，主要包括：

**食材废料：**
- 菜叶、菜根、果皮
- 蛋壳、鱼骨、肉骨等

**剩菜剩饭：**
- 米饭、面条等主食
- 汤汤水水、调料等

**过期食品：**
- 过期的面包、饼干
- 变质的水果、蔬菜等

**其他厨余：**
- 茶叶渣、咖啡渣
- 中药渣等

**投放要求：**
1. 沥干水分后投放
2. 有包装的需要去除包装
3. 大块骨头建议敲碎
4. 不要混入餐具、纸巾等

**处理价值：**
厨余垃圾经过处理可以制成有机肥料，实现资源循环利用。''',
                'category': '厨余垃圾',
                'image_url': '',
                'author_id': 1
            },
            {
                'title': '其他垃圾识别方法',
                'content': '''其他垃圾是指除可回收垃圾、有害垃圾、厨余垃圾之外的生活垃圾，主要包括：

**卫生用品：**
- 卫生纸、纸尿裤
- 卫生巾、湿纸巾等

**陶瓷制品：**
- 破碎的陶瓷、瓷器
- 花盆、瓦片等

**灰土类：**
- 烟蒂、煤渣
- 建筑垃圾、灰土等

**复合材料：**
- 污损的塑料袋
- 胶带、贴纸
- 保鲜膜等

**其他难分类物品：**
- 头发、宠物粪便
- 猫砂、尘土等

**判断原则：**
当不确定垃圾属于哪一类时，可以按照"其他垃圾"处理。其他垃圾一般采用卫生填埋或焚烧发电的方式处理。

**投放提示：**
尽量沥干水分，避免包装袋破损造成二次污染。''',
                'category': '其他垃圾',
                'image_url': '',
                'author_id': 1
            },
            {
                'title': '垃圾分类的环保意义',
                'content': '''垃圾分类是环境保护的重要举措，具有深远的环保意义：

**减少环境污染：**
- 有害垃圾单独处理，避免土壤和水源污染
- 减少垃圾填埋场的环境压力
- 降低焚烧产生的有害气体排放

**资源循环利用：**
- 可回收垃圾重新加工利用
- 厨余垃圾制成有机肥料
- 减少对原生资源的开采需求

**节约处理成本：**
- 提高垃圾处理效率
- 降低处理设施运营成本
- 延长填埋场使用寿命

**促进可持续发展：**
- 培养公众环保意识
- 推动循环经济发展
- 为子孙后代留下更好的环境

**个人行动建议：**
1. 从源头减少垃圾产生
2. 正确分类投放垃圾
3. 选择环保产品
4. 参与环保宣传活动

让我们共同努力，为建设美丽中国贡献力量！''',
                'category': '环保知识',
                'image_url': '',
                'author_id': 1
            }
        ]
        
        for article_data in default_articles:
            article = KnowledgeArticle(**article_data)
            db.session.add(article)
        
        db.session.commit()
        print("已添加默认科普知识文章")

# ==================== 积分系统相关函数 ====================

def get_or_create_user_points(user_id):
    """获取或创建用户积分记录"""
    user_points = UserPoints.query.filter_by(user_id=user_id).first()
    if not user_points:
        user_points = UserPoints(user_id=user_id)
        db.session.add(user_points)
        db.session.commit()
    return user_points

def add_points(user_id, points, reason, description=""):
    """为用户添加积分"""
    user_points = get_or_create_user_points(user_id)
    user_points.points += points
    user_points.total_earned += points
    
    # 更新等级
    update_user_level(user_points)
    
    # 记录积分变动
    points_history = PointsHistory(
        user_id=user_id,
        points_change=points,
        reason=reason,
        description=description
    )
    db.session.add(points_history)
    db.session.commit()
    
    # 检查成就
    check_achievements(user_id)
    
    return user_points

def update_user_level(user_points):
    """根据总积分更新用户等级"""
    total_points = user_points.total_earned
    
    if total_points >= 10000:
        user_points.level = 5
        user_points.level_name = "环保大师"
    elif total_points >= 5000:
        user_points.level = 4
        user_points.level_name = "环保专家"
    elif total_points >= 2000:
        user_points.level = 3
        user_points.level_name = "分类达人"
    elif total_points >= 500:
        user_points.level = 2
        user_points.level_name = "环保学徒"
    else:
        user_points.level = 1
        user_points.level_name = "环保新手"

def check_achievements(user_id):
    """检查用户是否达成新成就"""
    user_stats = get_or_create_user_stats(user_id)
    achievements = Achievement.query.filter_by(is_active=True).all()
    
    for achievement in achievements:
        # 检查用户是否已获得此成就
        existing = UserAchievement.query.filter_by(
            user_id=user_id, 
            achievement_id=achievement.id
        ).first()
        
        if existing:
            continue
            
        # 检查成就条件
        achieved = False
        if achievement.condition_type == 'prediction_count':
            achieved = user_stats.total_predictions >= achievement.condition_value
        elif achievement.condition_type == 'consecutive_days':
            achieved = user_stats.consecutive_days >= achievement.condition_value
        elif achievement.condition_type == 'accuracy_rate':
            achieved = user_stats.accuracy_rate >= achievement.condition_value
        
        if achieved:
            # 授予成就
            user_achievement = UserAchievement(
                user_id=user_id,
                achievement_id=achievement.id
            )
            db.session.add(user_achievement)
            
            # 奖励积分
            if achievement.points_reward > 0:
                add_points(user_id, achievement.points_reward, 
                          f"获得成就：{achievement.name}", 
                          achievement.description)
    
    db.session.commit()

def get_or_create_user_stats(user_id):
    """获取或创建用户统计记录"""
    user_stats = UserStats.query.filter_by(user_id=user_id).first()
    if not user_stats:
        user_stats = UserStats(user_id=user_id)
        db.session.add(user_stats)
        db.session.commit()
    return user_stats

def update_user_stats(user_id, predicted_class, is_correct=True):
    """更新用户统计数据"""
    user_stats = get_or_create_user_stats(user_id)
    user_stats.total_predictions += 1
    
    if is_correct:
        user_stats.correct_predictions += 1
    
    # 更新准确率
    if user_stats.total_predictions > 0:
        user_stats.accuracy_rate = user_stats.correct_predictions / user_stats.total_predictions
    
    # 更新最常识别的垃圾类型
    category_counts = {}
    predictions = PredictionHistory.query.filter_by(user_id=user_id).all()
    for pred in predictions:
        category = pred.predicted_class
        category_counts[category] = category_counts.get(category, 0) + 1
    
    if category_counts:
        user_stats.favorite_category = max(category_counts, key=category_counts.get)
    
    # 更新连续使用天数
    today = datetime.date.today()
    if user_stats.last_active_date:
        if user_stats.last_active_date == today - datetime.timedelta(days=1):
            user_stats.consecutive_days += 1
        elif user_stats.last_active_date != today:
            user_stats.consecutive_days = 1
    else:
        user_stats.consecutive_days = 1
    
    user_stats.last_active_date = today
    db.session.commit()

# ==================== 新增路由 ====================

# 用户个人中心
@app.route('/profile')
def profile():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    user = db.session.get(User, session['user_id'])
    user_points = get_or_create_user_points(user.id)
    user_stats = get_or_create_user_stats(user.id)
    
    # 获取用户成就
    user_achievements = db.session.query(UserAchievement, Achievement).join(
        Achievement, UserAchievement.achievement_id == Achievement.id
    ).filter(UserAchievement.user_id == user.id).all()
    
    # 获取最近的积分记录
    recent_points = PointsHistory.query.filter_by(user_id=user.id).order_by(
        PointsHistory.created_at.desc()
    ).limit(10).all()
    
    return render_template('profile.html', 
                         user=user, 
                         user_points=user_points, 
                         user_stats=user_stats,
                         user_achievements=user_achievements,
                         recent_points=recent_points)

# 签到功能
@app.route('/checkin', methods=['POST'])
def checkin():
    if 'user_id' not in session:
        return jsonify({'error': '请先登录'}), 401
    
    user_id = session['user_id']
    today = datetime.date.today()
    
    # 检查今天是否已签到
    existing_checkin = UserCheckin.query.filter_by(
        user_id=user_id, 
        checkin_date=today
    ).first()
    
    if existing_checkin:
        return jsonify({'error': '今天已经签到过了'}), 400
    
    # 计算连续签到天数
    yesterday = today - datetime.timedelta(days=1)
    yesterday_checkin = UserCheckin.query.filter_by(
        user_id=user_id, 
        checkin_date=yesterday
    ).first()
    
    consecutive_days = 1
    if yesterday_checkin:
        consecutive_days = yesterday_checkin.consecutive_days + 1
    
    # 计算签到奖励积分（连续签到有额外奖励）
    base_points = 5
    bonus_points = min(consecutive_days - 1, 10)  # 最多额外10积分
    total_points = base_points + bonus_points
    
    # 创建签到记录
    checkin = UserCheckin(
        user_id=user_id,
        checkin_date=today,
        points_earned=total_points,
        consecutive_days=consecutive_days
    )
    db.session.add(checkin)
    
    # 添加积分
    add_points(user_id, total_points, "每日签到", 
              f"连续签到{consecutive_days}天，获得{total_points}积分")
    
    return jsonify({
        'success': True,
        'points_earned': total_points,
        'consecutive_days': consecutive_days,
        'message': f'签到成功！获得{total_points}积分'
    })

# 积分商城
@app.route('/points_shop')
def points_shop():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    user_points = get_or_create_user_points(session['user_id'])
    shop_items = PointsShopItem.query.filter_by(is_available=True).all()
    
    return render_template('points_shop.html', user_points=user_points, shop_items=shop_items)

# 兑换商品
@app.route('/points_shop/redeem', methods=['POST'])
def redeem_item():
    if 'user_id' not in session:
        flash('请先登录', 'error')
        return redirect(url_for('login'))
    
    item_id = request.form.get('item_id')
    if not item_id:
        flash('商品ID无效', 'error')
        return redirect(url_for('points_shop'))
    
    item_id = int(item_id)
    
    user_id = session['user_id']
    user_points = get_or_create_user_points(user_id)
    item = PointsShopItem.query.get_or_404(item_id)
    
    if not item.is_available:
        flash('商品不可兑换', 'error')
        return redirect(url_for('points_shop'))
    
    if user_points.points < item.points_cost:
        flash('积分不足', 'error')
        return redirect(url_for('points_shop'))
    
    if item.stock == 0:
        flash('商品库存不足', 'error')
        return redirect(url_for('points_shop'))
    
    # 扣除积分
    user_points.points -= item.points_cost
    
    # 更新库存
    if item.stock > 0:
        item.stock -= 1
    
    # 创建兑换记录
    purchase = UserPurchase(
        user_id=user_id,
        item_id=item_id,
        points_spent=item.points_cost
    )
    db.session.add(purchase)
    
    # 记录积分变动
    points_history = PointsHistory(
        user_id=user_id,
        points_change=-item.points_cost,
        reason=f"兑换商品：{item.name}",
        description=item.description
    )
    db.session.add(points_history)
    
    db.session.commit()
    
    flash(f'成功兑换{item.name}！剩余积分：{user_points.points}', 'success')
    return redirect(url_for('points_shop'))

# 数据统计页面
@app.route('/statistics')
def statistics():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    user = db.session.get(User, session['user_id'])
    if not user.is_admin():
        flash('权限不足', 'error')
        return redirect(url_for('index'))
    
    # 获取统计数据
    total_users = User.query.count()
    total_predictions = PredictionHistory.query.count()
    total_articles = KnowledgeArticle.query.count()
    
    # 最近7天的数据
    seven_days_ago = datetime.date.today() - datetime.timedelta(days=7)
    recent_stats_raw = SystemStats.query.filter(
        SystemStats.date >= seven_days_ago
    ).order_by(SystemStats.date).all()
    
    # 转换为JSON可序列化的格式
    recent_stats = [{
        'id': stat.id,
        'date': stat.date.isoformat() if stat.date else None,
        'active_users': stat.active_users,
        'total_predictions': stat.total_predictions,
        'new_users': stat.new_users,
        'popular_category': stat.popular_category,
        'created_at': stat.created_at.isoformat() if stat.created_at else None
    } for stat in recent_stats_raw]
    
    # 垃圾分类统计
    category_stats_raw = db.session.query(
        PredictionHistory.predicted_class,
        db.func.count(PredictionHistory.id).label('count')
    ).group_by(PredictionHistory.predicted_class).all()
    
    # 转换为JSON可序列化的格式
    category_stats = [{
        'predicted_class': row.predicted_class,
        'count': row.count
    } for row in category_stats_raw]
    
    # 用户活跃度统计（按小时分布）
    user_activity = []
    for hour in range(24):
        # 模拟每小时的活跃用户数（示例数据）
        if 6 <= hour <= 23:  # 白天时间段活跃度较高
            count = random.randint(1, min(total_users, 8))
        else:  # 夜间时间段活跃度较低
            count = random.randint(0, min(total_users, 3))
        
        user_activity.append({
            'hour': hour,
            'count': count
        })
    
    # 计算今日活跃用户数
    today = datetime.datetime.now().date()
    active_users_today = db.session.query(User.id).join(PredictionHistory).filter(
        db.func.date(PredictionHistory.created_at) == today
    ).distinct().count()
    
    # 计算平均准确率（假设为示例值，可根据实际需求调整）
    average_accuracy = 0.85  # 可以根据实际的准确率计算逻辑来设置
    
    # 创建系统统计对象
    system_stats = {
        'total_users': total_users,
        'total_predictions': total_predictions,
        'active_users_today': active_users_today,
        'average_accuracy': average_accuracy
    }
    
    # 创建每日趋势数据（示例数据，可根据实际需求调整）
    daily_trend = []
    for i in range(7):
        date = datetime.date.today() - datetime.timedelta(days=6-i)
        daily_trend.append({
            'date': date.isoformat(),
            'count': total_predictions // 7 + (i * 5),  # 示例数据
            'users': total_users // 7 + (i * 2)  # 示例数据
        })
    
    # 准确率分布数据（示例数据）- 模板期望的是简单数组
    accuracy_distribution = [
        total_predictions // 10,  # 0-20%
        total_predictions // 8,   # 21-40%
        total_predictions // 5,   # 41-60%
        total_predictions // 3,   # 61-80%
        total_predictions // 4    # 81-100%
    ]
    
    # 用户排行榜 - 按预测次数排序
    top_users_raw = db.session.query(
        User.username,
        db.func.count(PredictionHistory.id).label('total_predictions')
    ).join(PredictionHistory).group_by(User.id).order_by(
        db.func.count(PredictionHistory.id).desc()
    ).limit(10).all()
    
    top_users = [{
        'username': row.username,
        'total_predictions': row.total_predictions
    } for row in top_users_raw]
    
    return render_template('statistics.html',
                         system_stats=system_stats,
                         total_users=total_users,
                         total_predictions=total_predictions,
                         total_articles=total_articles,
                         recent_stats=recent_stats,
                         category_stats=category_stats,
                         user_activity=user_activity,
                         daily_trend=daily_trend,
                         accuracy_distribution=accuracy_distribution,
                         top_users=top_users)

# 论坛首页
@app.route('/forum')
def forum():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    category = request.args.get('category', 'all')
    page = request.args.get('page', 1, type=int)
    
    query = ForumPost.query
    if category != 'all':
        query = query.filter_by(category=category)
    
    posts = query.order_by(
        ForumPost.is_pinned.desc(),
        ForumPost.created_at.desc()
    ).paginate(page=page, per_page=10, error_out=False)
    
    return render_template('forum.html', posts=posts, current_category=category)

# 发布帖子
@app.route('/forum/new_post', methods=['GET', 'POST'])
def new_post():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    if request.method == 'POST':
        title = request.form.get('title')
        content = request.form.get('content')
        category = request.form.get('category', 'general')
        
        if not title or not content:
            flash('标题和内容不能为空', 'error')
            return render_template('new_post.html')
        
        post = ForumPost(
            title=title,
            content=content,
            category=category,
            author_id=session['user_id']
        )
        db.session.add(post)
        db.session.commit()
        
        # 奖励积分
        add_points(session['user_id'], 10, "发布帖子", f"发布帖子：{title}")
        
        flash('帖子发布成功！', 'success')
        return redirect(url_for('forum'))
    
    return render_template('new_post.html')

# 查看帖子详情
@app.route('/forum/post/<int:post_id>', methods=['GET', 'POST'])
def view_post(post_id):
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    current_user = db.session.get(User, session['user_id'])
    
    if request.method == 'POST':
        # 处理回复提交
        content = request.form.get('content', '').strip()
        if content:
            reply = ForumReply(
                content=content,
                post_id=post_id,
                author_id=current_user.id
            )
            db.session.add(reply)
            
            # 更新帖子回复数
            post = ForumPost.query.get(post_id)
            if post:
                post.reply_count = ForumReply.query.filter_by(post_id=post_id).count() + 1
            
            db.session.commit()
            flash('回复发表成功！', 'success')
        else:
            flash('回复内容不能为空', 'error')
        
        return redirect(url_for('view_post', post_id=post_id))
    
    post = ForumPost.query.get_or_404(post_id)
    post.view_count += 1
    
    replies = ForumReply.query.filter_by(post_id=post_id).order_by(
        ForumReply.created_at
    ).all()
    
    db.session.commit()
    
    return render_template('view_post.html', post=post, replies=replies, current_user=current_user)

# 回复帖子
@app.route('/forum/post/<int:post_id>/reply', methods=['POST'])
def reply_post(post_id):
    if 'user_id' not in session:
        return jsonify({'error': '请先登录'}), 401
    
    content = request.form.get('content')
    if not content:
        return jsonify({'error': '回复内容不能为空'}), 400
    
    post = ForumPost.query.get_or_404(post_id)
    
    reply = ForumReply(
        post_id=post_id,
        author_id=session['user_id'],
        content=content
    )
    db.session.add(reply)
    
    # 更新帖子回复数
    post.reply_count += 1
    
    db.session.commit()
    
    # 奖励积分
    add_points(session['user_id'], 5, "回复帖子", f"回复帖子：{post.title}")
    
    return jsonify({'success': True, 'message': '回复成功！'})

# 点赞功能
# 帖子点赞
@app.route('/forum/like/<int:post_id>', methods=['POST'])
def toggle_post_like(post_id):
    if 'user_id' not in session:
        return jsonify({'error': '请先登录'}), 401
    
    user_id = session['user_id']
    
    # 检查是否已点赞
    existing_like = UserLike.query.filter_by(
        user_id=user_id,
        target_type='post',
        target_id=post_id
    ).first()
    
    if existing_like:
        # 取消点赞
        db.session.delete(existing_like)
        post = ForumPost.query.get(post_id)
        if post:
            post.like_count = max(0, post.like_count - 1)
        db.session.commit()
        return jsonify({'success': True, 'liked': False, 'likes_count': post.like_count if post else 0})
    else:
        # 添加点赞
        like = UserLike(
            user_id=user_id,
            target_type='post',
            target_id=post_id
        )
        db.session.add(like)
        post = ForumPost.query.get(post_id)
        if post:
            post.like_count += 1
        db.session.commit()
        return jsonify({'success': True, 'liked': True, 'likes_count': post.like_count if post else 1})

# 回复点赞
@app.route('/forum/reply/like/<int:reply_id>', methods=['POST'])
def toggle_reply_like(reply_id):
    if 'user_id' not in session:
        return jsonify({'error': '请先登录'}), 401
    
    user_id = session['user_id']
    
    # 检查是否已点赞
    existing_like = UserLike.query.filter_by(
        user_id=user_id,
        target_type='reply',
        target_id=reply_id
    ).first()
    
    if existing_like:
        # 取消点赞
        db.session.delete(existing_like)
        reply = ForumReply.query.get(reply_id)
        if reply:
            reply.like_count = max(0, reply.like_count - 1)
        db.session.commit()
        return jsonify({'success': True, 'liked': False, 'likes_count': reply.like_count if reply else 0})
    else:
        # 添加点赞
        like = UserLike(
            user_id=user_id,
            target_type='reply',
            target_id=reply_id
        )
        db.session.add(like)
        reply = ForumReply.query.get(reply_id)
        if reply:
            reply.like_count += 1
        db.session.commit()
        return jsonify({'success': True, 'liked': True, 'likes_count': reply.like_count if reply else 1})

@app.route('/like/<target_type>/<int:target_id>', methods=['POST'])
def toggle_like(target_type, target_id):
    if 'user_id' not in session:
        return jsonify({'error': '请先登录'}), 401
    
    user_id = session['user_id']
    
    # 检查是否已点赞
    existing_like = UserLike.query.filter_by(
        user_id=user_id,
        target_type=target_type,
        target_id=target_id
    ).first()
    
    if existing_like:
        # 取消点赞
        db.session.delete(existing_like)
        
        if target_type == 'post':
            post = ForumPost.query.get(target_id)
            if post:
                post.like_count = max(0, post.like_count - 1)
        elif target_type == 'reply':
            reply = ForumReply.query.get(target_id)
            if reply:
                reply.like_count = max(0, reply.like_count - 1)
        
        db.session.commit()
        return jsonify({'success': True, 'action': 'unliked'})
    else:
        # 添加点赞
        like = UserLike(
            user_id=user_id,
            target_type=target_type,
            target_id=target_id
        )
        db.session.add(like)
        
        if target_type == 'post':
            post = ForumPost.query.get(target_id)
            if post:
                post.like_count += 1
        elif target_type == 'reply':
            reply = ForumReply.query.get(target_id)
            if reply:
                reply.like_count += 1
        
        db.session.commit()
        return jsonify({'success': True, 'action': 'liked'})

# 在既有statistics路由之后添加导出功能路由
@app.route('/statistics/export')
def export_statistics():
    if 'user_id' not in session:
        return redirect(url_for('login'))
    
    user = db.session.get(User, session['user_id'])
    if not user.is_admin():
        flash('权限不足', 'error')
        return redirect(url_for('index'))
    
    # 获取导出格式
    format_type = request.args.get('format', 'excel')
    
    # 获取统计数据
    total_users = User.query.count()
    total_predictions = PredictionHistory.query.count()
    
    # 垃圾分类统计
    category_stats = db.session.query(
        PredictionHistory.predicted_class,
        db.func.count(PredictionHistory.id).label('count')
    ).group_by(PredictionHistory.predicted_class).all()
    
    category_data = [{
        'category': row.predicted_class,
        'count': row.count
    } for row in category_stats]
    
    # 最近7天的数据
    seven_days_ago = datetime.date.today() - datetime.timedelta(days=7)
    daily_stats = []
    
    for i in range(7):
        date = seven_days_ago + datetime.timedelta(days=i)
        count = PredictionHistory.query.filter(
            db.func.date(PredictionHistory.created_at) == date
        ).count()
        
        daily_stats.append({
            'date': date.strftime('%Y-%m-%d'),
            'count': count
        })
    
    # 用户排行榜
    top_users = db.session.query(
        User.username,
        db.func.count(PredictionHistory.id).label('total_predictions')
    ).join(PredictionHistory).group_by(User.id).order_by(
        db.func.count(PredictionHistory.id).desc()
    ).limit(10).all()
    
    top_users_data = [{
        'username': row.username,
        'total_predictions': row.total_predictions
    } for row in top_users]
    
    # 根据导出格式进行处理
    if format_type == 'excel':
        import io
        import xlsxwriter
        from flask import send_file
        
        # 创建一个内存中的Excel文件
        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output)
        
        # 添加表头样式
        header_format = workbook.add_format({
            'bold': True,
            'align': 'center',
            'valign': 'vcenter',
            'fg_color': '#D9EAD3',
            'border': 1
        })
        
        # 数据样式
        data_format = workbook.add_format({
            'align': 'center',
            'valign': 'vcenter',
            'border': 1
        })
        
        # 系统概览工作表
        overview_sheet = workbook.add_worksheet('系统概览')
        overview_data = [
            ['总用户数', '总识别次数'],
            [total_users, total_predictions]
        ]
        
        overview_sheet.set_column('A:B', 15)
        for row_num, row_data in enumerate(overview_data):
            for col_num, col_data in enumerate(row_data):
                if row_num == 0:
                    overview_sheet.write(row_num, col_num, col_data, header_format)
                else:
                    overview_sheet.write(row_num, col_num, col_data, data_format)
        
        # 垃圾分类工作表
        category_sheet = workbook.add_worksheet('垃圾分类统计')
        category_sheet.write_row(0, 0, ['垃圾类型', '识别次数'], header_format)
        category_sheet.set_column('A:B', 15)
        
        for row_num, item in enumerate(category_data):
            category_sheet.write(row_num + 1, 0, item['category'], data_format)
            category_sheet.write(row_num + 1, 1, item['count'], data_format)
        
        # 每日趋势工作表
        daily_sheet = workbook.add_worksheet('每日识别趋势')
        daily_sheet.write_row(0, 0, ['日期', '识别次数'], header_format)
        daily_sheet.set_column('A:B', 15)
        
        for row_num, item in enumerate(daily_stats):
            daily_sheet.write(row_num + 1, 0, item['date'], data_format)
            daily_sheet.write(row_num + 1, 1, item['count'], data_format)
        
        # 用户排行榜工作表
        users_sheet = workbook.add_worksheet('用户排行榜')
        users_sheet.write_row(0, 0, ['排名', '用户名', '识别次数'], header_format)
        users_sheet.set_column('A:C', 15)
        
        for row_num, item in enumerate(top_users_data):
            users_sheet.write(row_num + 1, 0, row_num + 1, data_format)
            users_sheet.write(row_num + 1, 1, item['username'], data_format)
            users_sheet.write(row_num + 1, 2, item['total_predictions'], data_format)
        
        workbook.close()
        output.seek(0)
        
        # 生成下载文件名
        today = datetime.datetime.now().strftime('%Y%m%d')
        filename = f"垃圾分类系统数据统计_{today}.xlsx"
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
    
    elif format_type == 'pdf':
        from flask import make_response
        import pdfkit
        
        # 创建PDF内容
        html_content = f"""
        <html>
        <head>
            <meta charset="UTF-8">
            <title>垃圾分类系统数据统计</title>
            <style>
                body {{ font-family: Arial, sans-serif; }}
                h1 {{ color: #4CAF50; text-align: center; }}
                h2 {{ color: #45B7D1; margin-top: 20px; }}
                table {{ border-collapse: collapse; width: 100%; margin-bottom: 20px; }}
                th, td {{ padding: 8px; text-align: left; border: 1px solid #ddd; }}
                th {{ background-color: #f2f2f2; }}
            </style>
        </head>
        <body>
            <h1>垃圾分类系统数据统计</h1>
            <p>生成时间: {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p>
            
            <h2>系统概览</h2>
            <table>
                <tr>
                    <th>总用户数</th>
                    <th>总识别次数</th>
                </tr>
                <tr>
                    <td>{total_users}</td>
                    <td>{total_predictions}</td>
                </tr>
            </table>
            
            <h2>垃圾分类统计</h2>
            <table>
                <tr>
                    <th>垃圾类型</th>
                    <th>识别次数</th>
                </tr>
                {"".join([f"<tr><td>{item['category']}</td><td>{item['count']}</td></tr>" for item in category_data])}
            </table>
            
            <h2>每日识别趋势</h2>
            <table>
                <tr>
                    <th>日期</th>
                    <th>识别次数</th>
                </tr>
                {"".join([f"<tr><td>{item['date']}</td><td>{item['count']}</td></tr>" for item in daily_stats])}
            </table>
            
            <h2>用户排行榜</h2>
            <table>
                <tr>
                    <th>排名</th>
                    <th>用户名</th>
                    <th>识别次数</th>
                </tr>
                {"".join([f"<tr><td>{i+1}</td><td>{item['username']}</td><td>{item['total_predictions']}</td></tr>" for i, item in enumerate(top_users_data)])}
            </table>
        </body>
        </html>
        """
        
        try:
            # 尝试使用pdfkit生成PDF
            pdf = pdfkit.from_string(html_content, False)
            
            response = make_response(pdf)
            response.headers['Content-Type'] = 'application/pdf'
            response.headers['Content-Disposition'] = 'attachment; filename=垃圾分类系统数据统计.pdf'
            
            return response
        except Exception as e:
            # 如果PDF生成失败，可能是没有安装wkhtmltopdf
            print(f"PDF生成失败: {e}")
            flash('PDF生成失败，请确保服务器已安装wkhtmltopdf', 'error')
            return redirect(url_for('statistics'))
    
    else:
        flash('不支持的导出格式', 'error')
        return redirect(url_for('statistics'))

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=True)